package leesoft.hub;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sisuntech.common.ValueBase;

import jxl.write.*;
import jxl.Workbook;
import leesoft.toolbox.export.templates.Field;
import leesoft.toolbox.export.templates.IDBValueTemplate;

public class ExportServlet extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public ExportServlet() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		try{

			String exportTemplate=request.getParameter("exportTemplate");
			
			String sql=request.getParameter("condition");
			
			IDBValueTemplate template=(IDBValueTemplate)Class.forName(exportTemplate).newInstance();
			template.setQuery(sql);
			
			response.setContentType("application/vnd.ms-excel");
			response.addHeader("Content-Disposition","attachment;filename=export.xls");
			WritableWorkbook wbook=Workbook.createWorkbook(response.getOutputStream());
			WritableSheet wsheet = wbook.createSheet("查询结果", 0); // sheet name
			jxl.write.WritableFont wfont = null; // 字体
			jxl.write.WritableCellFormat wcfFC = null; // 字体格式
			jxl.write.Label wlabel = null; // Excel表格的Cell
			
			wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD,
				     false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
			wcfFC = new jxl.write.WritableCellFormat(wfont);
			int col=0;
			int row=0;
			for(Field field:template.getFields()){
				wlabel = new jxl.write.Label(col, row, field.getFieldName());
				wsheet.addCell(wlabel);
				col++;
			}
			
			
			wfont = new jxl.write.WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD,
				    false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
			wcfFC = new jxl.write.WritableCellFormat(wfont);
			
			List values=template.getValues();
			
			row=1;
			
			for(Object value:values){
				ValueBase vb=(ValueBase)value;
				
				col=0;
				for(Field field:template.getFields()){
					wlabel = new jxl.write.Label(col, row, vb.getStringValue(field.getField()));
					wsheet.addCell(wlabel);
					col++;
				}
				
				row++;
			}
			wbook.write(); // 写入文件
			wbook.close();
			response.getOutputStream().flush();
			response.getOutputStream().close();
		}catch(WriteException e){
			e.printStackTrace();
		}catch(InstantiationException e){
			e.printStackTrace();
		}catch(IllegalAccessException e){
			e.printStackTrace();
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
	}

	/**
	 * Initialization of the servlet. <br>
	 *
	 * @throws ServletException if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}
	private String getRangeValue(String rangeValue){
		return "'"+rangeValue.replaceAll(",", "','")+"'";
	}

}
